package edu.hawaii.myisern.model;

import java.io.File;
import java.io.StringWriter;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.xml.bind.JAXBContext;
import javax.xml.bind.Marshaller;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;

import edu.hawaii.myisern.collaborations.jaxb.CollaboratingOrganizations;
import edu.hawaii.myisern.collaborations.jaxb.Collaboration;
import edu.hawaii.myisern.collaborations.jaxb.CollaborationTypes;
import edu.hawaii.myisern.collaborations.jaxb.Collaborations;
import edu.hawaii.myisern.collaborations.jaxb.OutcomeTypes;
import edu.hawaii.myisern.collaborations.jaxb.Years;
import edu.hawaii.myisern.organizations.jaxb.AffiliatedResearchers;
import edu.hawaii.myisern.organizations.jaxb.Organization;
import edu.hawaii.myisern.organizations.jaxb.Organizations;
import edu.hawaii.myisern.organizations.jaxb.ResearchKeywords;
import edu.hawaii.myisern.researchers.jaxb.Researcher;
import edu.hawaii.myisern.researchers.jaxb.Researchers;



/** 
 * Provides a persistent store for our Stack using embedded Derby.
 * Not intended as an example of good database design.
 * Instead, just shows how to get embed a DB backend within the Model with minimal example.  
 * 
 * Note: In embedded mode, connections are "free", so we make no effort to manage them via pooling.
 * 
 * @author Philip Johnson
 */
public class DerbyMyIsernStore {

  /** The JDBC driver. */
  private static final String driver = "org.apache.derby.jdbc.EmbeddedDriver";
  
  /**  The Derby connection URL. */ 
  private static final String connectionURL = "jdbc:derby:myisern-pink-db;create=true";
  
  /** The key for putting/retrieving the directory where Derby will create its databases. */
  private static final String derbySystemKey = "derby.system.home";
  
  
  /** Holds the class-wide JAXBContext, which is thread-safe. */
  private JAXBContext collaborationsJaxbContext;
  /** Holds the class-wide JAXBContext, which is thread-safe. */
  private JAXBContext organizationsJaxbContext;
  /** Holds the class-wide JAXBContext, which is thread-safe. */
  private JAXBContext researchersJaxbContext;
  
  /** Holds the class-wide Collaborations, which is thread-safe. */
  private Collaborations collaborations;
  /** Holds the class-wide Organizations, which is thread-safe. */
  private Organizations organizations;
  /** Holds the class-wide Researchers, which is thread-safe. */
  private Researchers researchers;
  
  /** An error string, always displayed, but invisible if empty. */
  private String errorMessage = "";
  
  String name = "Name";
  String cama = "','";
  String whereName = "' where Name = '";
  String closeString = "Close faile!!!";
  String deleteString = "Delete faile!!!";
  String collaborationName = "CollaborationName";
  String col_values = "CollaborationName) values ('";
  String tableExist = "Table exists";
  String idKey = "(ID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, ";
  String org_values = "OrganizationName) values ('";
  
  /**
   * Creates the persistent store for the Stack in ~/.myisern-pink-db/db. 
   */
  public DerbyMyIsernStore () {
    // Make sure the directory exists, creating it if not present.
    File myIsernHome = new File(System.getProperty("user.home"), ".myisern-pink-db");
    File dbHome = new File(myIsernHome, "db");
    dbHome.mkdirs();
    // Set the system property used by Derby to locate the db files. 
    System.getProperties().put(derbySystemKey, dbHome.getAbsolutePath());
    boolean derbyPath = true;
    // Now load the Derby driver. 
    try {
      Class.forName(driver); 
      derbyPath = true;
    } 
    catch (ClassNotFoundException e) {
      String msg = "Exception during DerbyMyIsernStore initialization: " +
          "Derby not on CLASSPATH.";
      derbyPath = false;
      throw new RuntimeException(msg, e);
    }
    if (derbyPath = true) {
      try {
        this.init();
      }
      catch (Exception e) {
        this.errorMessage = "Initialization false!";
      }
    }
    
  }
  
  /**
   * Builds and returns a new researchers instance by reading values from the Derby DB.
   * If the DB cannot be read, then an empty researchers instance is returned.
   * 
   * @return researchers The researchers instance retrieved from the database, 
   *          or an empty researchers if none found.
   */
  private Researchers getDbResearchers() {
    Researchers researchers = new Researchers();
    Researcher researcher = new Researcher();
    Connection conn = null;
    Statement statement = null;
    ResultSet results = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      results = statement.executeQuery("SELECT Name, Organization, Email, " +
          "PictureLink, BioStatement FROM RESEARCHERS ORDER BY Name");
      while (results.next()) {
        researcher = new Researcher();
        researcher.setName(results.getString(name));
        researcher.setOrganization(results.getString("Organization"));
        researcher.setEmail(results.getString("Email"));
        researcher.setPictureLink(results.getString("PictureLink"));
        researcher.setBioStatement(results.getString("BioStatement"));
        researchers.getResearcher().add(researcher);
      }
    }
    catch (SQLException e) { //NOPMD
      // Do nothing. In the normal case, this indicates the DB did not have anything in it. 
    }
    finally {
      try {
        results.close();
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // out of options.
      }
    }
    return researchers;
 
  }
  
  /**
   * Insert the researcher. 
   * 
   * @param researcher The researcher that will be inserted.
   * @return insert Return true if insert successful.
   */
  public boolean insertResearcher (Researcher researcher) {
    Connection conn = null;
    Statement statement = null;
    boolean insert = true;
    try {
      conn = DriverManager.getConnection(connectionURL);
      // createEmptyTable();
      statement = conn.createStatement();
      statement.executeUpdate("INSERT INTO RESEARCHERS (Name, Organization, Email, " +
          "PictureLink, BioStatement) values ('" + researcher.getName() + 
          cama + researcher.getOrganization() + cama + researcher.getEmail() + 
          cama + researcher.getPictureLink() + cama + researcher.getBioStatement() + "')");
      insert = true;
    }
    catch (SQLException e) {
      insert = false;
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
    return insert;
  }
  
  /**
   * Edits the researcher. 
   * @param researcher with the edited values.
   */
  public void editResearcher (Researcher researcher) {
    Connection conn = null;
    Statement statement = null;
     
    try {
      conn = DriverManager.getConnection(connectionURL);
      //createEmptyTable();
      statement = conn.createStatement();
      statement.executeUpdate("UPDATE RESEARCHERS SET Organization='" + 
          researcher.getOrganization() + whereName + researcher.getName() + "'");
      statement.executeUpdate("UPDATE RESEARCHERS SET Email='" + researcher.getEmail() +
          "' where Name='" + researcher.getName() + "'");
      statement.executeUpdate("UPDATE RESEARCHERS SET PictureLink='" + 
          researcher.getPictureLink() + "' where Name='" + researcher.getName() + "'");
      statement.executeUpdate("UPDATE RESEARCHERS SET BioStatement='" + 
          researcher.getBioStatement() + "' where Name='" + researcher.getName() + "'");     
    }
    catch (SQLException e) {
      this.errorMessage = "Update faile!!!";
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        this.errorMessage = closeString;
      }
    }
  }
  
  /**
   * Delete the researcher. 
   * 
   * @param name The stack to be persisted.
   */
  public void deleteResearcher (String name) {
    Connection conn = null;
    Statement statement = null;
     
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.executeUpdate("DELETE FROM RESEARCHERS WHERE Name='" + name + "'");
    }
    catch (SQLException e) {
      this.errorMessage = deleteString;
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        this.errorMessage = closeString;
      }
    }
  }
  
  /**
   * Builds and returns a new Organizations instance by reading values from the Derby DB.
   * If the DB cannot be read, then an empty Organizations instance is returned.
   * 
   * @return organizations The Organizations instance retrieved from the database, 
   *          or an empty Organizations if none found.
   */
  private Organizations getDbOrganizations() {
    Organizations organizations = new Organizations();
    Organization organization = new Organization();
    
    AffiliatedResearchers aff_Resears = new AffiliatedResearchers();
    ResearchKeywords resch_Kwords = new ResearchKeywords();
    
    Connection conn = null;
    Statement statement = null;
    Statement statement1 = null;
    Statement statement2 = null;
    
    ResultSet results = null;
    ResultSet af_results = null;
    ResultSet rk_results = null;
    
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      results = statement.executeQuery("SELECT Name, Type, Contact, Country, HomePage, " +
          "ResearchDescription FROM ORGANIZATIONS ORDER BY Name");
     
      
      while (results.next()) {
        organization = new Organization();
        String o_name = results.getString(name);
        organization.setName(results.getString(name));
        organization.setType(results.getString("Type"));
        organization.setContact(results.getString("Contact"));
        organization.setCountry(results.getString("Country"));
        organization.setHomePage(results.getString("HomePage"));
        organization.setResearchDescription(results.getString("ResearchDescription"));
       
        statement1 = conn.createStatement();
        af_results = statement1.executeQuery("SELECT AffiliatedReseacher, " +
          "OrganizationName FROM AFFILIATEDRESEARCHERS ORDER BY OrganizationName");
        aff_Resears = new AffiliatedResearchers();
        while (af_results.next()) {
          String af_name = af_results.getString("OrganizationName");
          if (af_name.equals(o_name)) {
            aff_Resears.getAffiliatedResearcher().add(af_results.getString("AffiliatedReseacher"));
          }
        }
        organization.setAffiliatedResearchers(aff_Resears);

        statement2 = conn.createStatement();
        rk_results = statement2.executeQuery("SELECT ReseachKeyword, " +
          "OrganizationName FROM RESEARCHKEYWORDS ORDER BY OrganizationName");
        
        resch_Kwords = new ResearchKeywords();
        while (rk_results.next()) {
          String rk_name = rk_results.getString("OrganizationName");
          if (rk_name.equals(o_name)) {
            resch_Kwords.getResearchKeyword().add(rk_results.getString("ReseachKeyword"));
          }
        }
        organization.setResearchKeywords(resch_Kwords);
        
        organizations.getOrganization().add(organization);
      }
    }
    catch (SQLException e) { //NOPMD
      // Do nothing. In the normal case, this indicates the DB did not have anything in it. 
    }
    finally {
      try {
        results.close();
        af_results.close();
        rk_results.close();
        statement.close();
        statement1.close();
        statement2.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        this.errorMessage = closeString;
      }
    }  
    return organizations;
  }
  
  /**
   * Insert an Organization. 
   * 
   * @param organization The organization that will be inserted.
   * @return insert true if insert successfully.
   */
  public boolean insertOrganization (Organization organization) {
    Connection conn = null;
    Statement statement = null;
    boolean insert = true;
    AffiliatedResearchers aff_Rs = organization.getAffiliatedResearchers();
    ResearchKeywords r_KWs = organization.getResearchKeywords();
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.executeUpdate("INSERT INTO ORGANIZATIONS (Name, Type, Contact, " +
          "Country, HomePage, ResearchDescription) values ('" + organization.getName() + 
          cama + organization.getType() + cama + organization.getContact() + 
          cama + organization.getCountry() + cama + organization.getHomePage() + 
          cama + organization.getResearchDescription() + "')");
      
      for (int i = 0; i < aff_Rs.getAffiliatedResearcher().size(); i++ ) {
        String aff_R = aff_Rs.getAffiliatedResearcher().get(i);
        statement.executeUpdate("INSERT INTO AFFILIATEDRESEARCHERS " +
            "(AffiliatedReseacher, " + org_values +
            aff_R + cama + organization.getName() + "')");
      }
      for (int i = 0; i < r_KWs.getResearchKeyword().size(); i++ ) {
        String r_KW = r_KWs.getResearchKeyword().get(i);
        statement.executeUpdate("INSERT INTO RESEARCHKEYWORDS (ReseachKeyword, " +
            org_values + r_KW + cama + organization.getName() + "')");
      }
      insert = true;
    }
    catch (SQLException e) {
      insert = false;
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        this.errorMessage = closeString;
      }
    }
    return insert;    
  }
  
  /**
   * Edits the organization. 
   * 
   * @param organization with the edited values.
   */
  public void editOrganization(Organization organization) {
    Connection conn = null;
    Statement statement = null;
    
    AffiliatedResearchers aff_Rs = organization.getAffiliatedResearchers();
    ResearchKeywords r_KWs = organization.getResearchKeywords();
    
    try {
      conn = DriverManager.getConnection(connectionURL);
      //createEmptyTable();
      statement = conn.createStatement();
      statement.executeUpdate("UPDATE ORGANIZATIONS SET Type='" + 
          organization.getType() + whereName + organization.getName() + "'");
      statement.executeUpdate("UPDATE ORGANIZATIONS SET Contact='" + 
          organization.getContact() + whereName + organization.getName() + "'");
      statement.executeUpdate("UPDATE ORGANIZATIONS SET Country='" + 
          organization.getCountry() + whereName + organization.getName() + "'");
      statement.executeUpdate("UPDATE ORGANIZATIONS SET HomePage='" + 
          organization.getHomePage() + whereName + organization.getName() + "'");     
      statement.executeUpdate("UPDATE ORGANIZATIONS SET ResearchDescription='" + 
          organization.getResearchDescription() + whereName + 
          organization.getName() + "'");
     
      statement.executeUpdate("DELETE FROM AFFILIATEDRESEARCHERS WHERE OrganizationName='"
          + organization.getName() + "'");
      for (int i = 0; i < aff_Rs.getAffiliatedResearcher().size(); i++ ) {
        String aff_R = aff_Rs.getAffiliatedResearcher().get(i);
        statement.executeUpdate("INSERT INTO AFFILIATEDRESEARCHERS (AffiliatedReseacher, " +
            org_values + aff_R + cama + organization.getName() + "')");
        aff_R = "";
      }
      
      statement.executeUpdate("DELETE FROM RESEARCHKEYWORDS WHERE OrganizationName='" +
          organization.getName() + "'");
      for (int i = 0; i < r_KWs.getResearchKeyword().size(); i++ ) {
        String r_KW = r_KWs.getResearchKeyword().get(i);
        statement.executeUpdate("INSERT INTO RESEARCHKEYWORDS (ReseachKeyword, " +
            org_values + r_KW + cama + organization.getName() + "')");
      }     
    }
    catch (SQLException e) {
      this.errorMessage = "Insert faile!!!";
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        this.errorMessage = closeString;
      }
    }
  }
  
  /**
   * Deletes an Organization form the Database. 
   * 
   * @param name The name of the organization to be deleted.
   * @return deleted Return true if delete successful.
   */
  public boolean deleteOrganization (String name) {
    Connection conn = null;
    Statement statement = null;
    boolean deleted = true;
     
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.executeUpdate("DELETE FROM ORGANIZATIONS WHERE Name='" + name + "'");
      statement.executeUpdate("DELETE FROM AFFILIATEDRESEARCHERS WHERE OrganizationName='"
          + name + "'");
      statement.executeUpdate("DELETE FROM RESEARCHKEYWORDS WHERE OrganizationName='"
          + name + "'");
    }
    catch (SQLException e) {
      this.errorMessage = deleteString;
      deleted = false;
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
    return deleted;
  }
  
  /**
   * Builds and returns a new Collaborations instance by reading values from the Derby DB.
   * If the DB cannot be read, then an empty Collaborations instance is returned.
   * 
   * @return Collaborations  The Collaborations instance retrieved from the database, 
   *          or an empty Collaborations if none found.
   */
  private Collaborations getDbCollaborations() {
    Collaborations collaborations = new Collaborations();
    Collaboration collaboration = new Collaboration();
    CollaboratingOrganizations cl_Orgs = new CollaboratingOrganizations();
    CollaborationTypes cl_Types = new CollaborationTypes();
    OutcomeTypes outcomeT = new OutcomeTypes();
    Years years = new Years();
    Connection conn = null;
    Statement statement = null;
    Statement statement1 = null;
    Statement statement2 = null;
    Statement statement3 = null;
    Statement statement4 = null;
    ResultSet results = null;
    ResultSet co_results = null;
    ResultSet ct_results = null;
    ResultSet ot_results = null;
    ResultSet y_results = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      results = statement.executeQuery("SELECT Name, Description FROM " +
          "COLLABORATIONS ORDER BY Name");
      
      while (results.next()) {
        collaboration = new Collaboration();
        String cl_name = results.getString(name);
        collaboration.setName(cl_name);
        collaboration.setDescription(results.getString("Description"));
        
        statement1 = conn.createStatement();
        co_results = statement1.executeQuery("SELECT CollaboratingOrganization, " +
            "CollaborationName FROM COLLABORATINGORGANIZATIONS ORDER BY CollaborationName");
        cl_Orgs = new CollaboratingOrganizations();
        while (co_results.next()) {
          String cllab_name = co_results.getString(collaborationName);
          if (cllab_name.equals(cl_name)) {
            String temp = co_results.getString("CollaboratingOrganization");
            cl_Orgs.getCollaboratingOrganization().add(temp);
          }
        }
        collaboration.setCollaboratingOrganizations(cl_Orgs);
        
        statement2 = conn.createStatement();
        ct_results = statement2.executeQuery("SELECT CollaborationType, " +
            "CollaborationName FROM COLLABORATINGTYPES ORDER BY CollaborationName");
        cl_Types = new CollaborationTypes();
        while (ct_results.next()) {
          String ct_name = ct_results.getString(collaborationName);
          if (ct_name.equals(cl_name)) {
            String temp = ct_results.getString("CollaborationType");
            cl_Types.getCollaborationType().add(temp);
          }
        }
        collaboration.setCollaborationTypes(cl_Types);
        
        statement3 = conn.createStatement();
        ot_results = statement3.executeQuery("SELECT OutcomeType, " +
            "CollaborationName FROM OUTCOMETYPES ORDER BY CollaborationName");
        outcomeT = new OutcomeTypes();
        while (ot_results.next()) {
          String ot_name = ot_results.getString(collaborationName);
          if (ot_name.equals(cl_name)) {
            String temp = ot_results.getString("OutcomeType");
            outcomeT.getOutcomeType().add(temp);
          }
        }
        collaboration.setOutcomeTypes(outcomeT);
        
        statement4 = conn.createStatement();
        y_results = statement4.executeQuery("SELECT Yearss, " +
            "CollaborationName FROM YEARS ORDER BY CollaborationName");
        years = new Years();
        while (y_results.next()) {           
          String y_name = y_results.getString(collaborationName);
          if (y_name.equals(cl_name)) {
            BigInteger temp = y_results.getBigDecimal("Yearss").toBigIntegerExact();
            years.getYear().add(temp);
          }
        }
        collaboration.setYears(years);
        collaborations.getCollaboration().add(collaboration);
      }
    }
    catch (SQLException e) { //NOPMD
      this.errorMessage = "Get collaborations faile!!!";
    }
    finally {
      try {
        results.close();
        co_results.close();
        ct_results.close();
        ot_results.close();
        y_results.close();
        statement.close();
        statement1.close();
        statement2.close();
        statement3.close();
        statement4.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        this.errorMessage = closeString;
      }
    }
    
    return collaborations;
 
  }
  
  /**
   * Insert Collaboration. 
   * 
   * @param collaboration The collaboration will be inserted.
   * @return insert Return true if insert successful.
   */
  public boolean insertCollaboration (Collaboration collaboration) {
    Connection conn = null;
    Statement statement = null;
    boolean insert = true;
    CollaboratingOrganizations col_Orgs = collaboration.getCollaboratingOrganizations();
    CollaborationTypes col_Tpyes = collaboration.getCollaborationTypes();
    OutcomeTypes o_Types = collaboration.getOutcomeTypes();
    Years years = collaboration.getYears();
    
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.executeUpdate("INSERT INTO COLLABORATIONS (Name, Description) values ('" + 
          collaboration.getName() + cama + collaboration.getDescription() + "')");
      
      for (int i = 0; i < col_Orgs.getCollaboratingOrganization().size(); i++ ) {
        String col_Org = col_Orgs.getCollaboratingOrganization().get(i);
        statement.executeUpdate("INSERT INTO COLLABORATINGORGANIZATIONS " +
            "(CollaboratingOrganization," + " CollaborationName) values ('" + 
            col_Org + cama + collaboration.getName() + "')");
      }
      
      for (int i = 0; i < col_Tpyes.getCollaborationType().size(); i++ ) {
        String col_Tpye = col_Tpyes.getCollaborationType().get(i);
        statement.executeUpdate("INSERT INTO COLLABORATINGTYPES (CollaborationType, " +
            col_values + col_Tpye + cama + collaboration.getName() + "')");
      }
      
      for (int i = 0; i < o_Types.getOutcomeType().size(); i++ ) {
        String o_Type = o_Types.getOutcomeType().get(i);
        statement.executeUpdate("INSERT INTO OUTCOMETYPES (OutcomeType, " +
            col_values + o_Type + cama + collaboration.getName() + "')");
      }
      
      for (int i = 0; i < years.getYear().size(); i++ ) {
        BigInteger year = years.getYear().get(i);
        statement.executeUpdate("INSERT INTO YEARS (Yearss, " + "CollaborationName) values (" +
            year +  ",'" + collaboration.getName() + "')");
      }
      
      insert = true;
    }
    catch (SQLException e) {
      insert = false;
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        this.errorMessage = closeString;
      }
    }
    return insert;
  }
  
  /**
   * Insert Collaboration. 
   * 
   * @param collaboration Return true if insert successful.
   * @return insert Return true of insert successful.
   */
  public boolean editCollaboration (Collaboration collaboration) {
    Connection conn = null;
    Statement statement = null;
    boolean insert = true;
    CollaboratingOrganizations col_Orgs = collaboration.getCollaboratingOrganizations();
    CollaborationTypes col_Tpyes = collaboration.getCollaborationTypes();
    OutcomeTypes o_Types = collaboration.getOutcomeTypes();
    Years years = collaboration.getYears();
    
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.executeUpdate("UPDATE COLLABORATIONS SET Description='" + 
          collaboration.getDescription() + whereName + collaboration.getName() + "'");
      
      statement.executeUpdate("DELETE FROM COLLABORATINGORGANIZATIONS WHERE CollaborationName='"
          + collaboration.getName() + "'");
      for (int i = 0; i < col_Orgs.getCollaboratingOrganization().size(); i ++ ) {
        String col_Org = col_Orgs.getCollaboratingOrganization().get(i);
        statement.executeUpdate("INSERT INTO COLLABORATINGORGANIZATIONS " +
            "(CollaboratingOrganization," + " CollaborationName) values ('" + 
            col_Org + cama + collaboration.getName() + "')");
      }
      
      statement.executeUpdate("DELETE FROM COLLABORATINGTYPES WHERE CollaborationName='" +
          collaboration.getName() + "'");
      for (int i = 0; i < col_Tpyes.getCollaborationType().size(); i ++ ) {
        String col_Tpye = col_Tpyes.getCollaborationType().get(i);
        statement.executeUpdate("INSERT INTO COLLABORATINGTYPES (CollaborationType, " +
            col_values + col_Tpye +  cama + collaboration.getName() + "')");
      }
      
      statement.executeUpdate("DELETE FROM OUTCOMETYPES WHERE CollaborationName='" + 
          collaboration.getName() + "'");
      for (int i = 0; i < o_Types.getOutcomeType().size(); i++ ) {
        String o_Type = o_Types.getOutcomeType().get(i);
        statement.executeUpdate("INSERT INTO OUTCOMETYPES (OutcomeType, " +
            col_values + o_Type + cama + collaboration.getName() + "')");
      }
      
      statement.executeUpdate("DELETE FROM YEARS WHERE CollaborationName='" + 
          collaboration.getName() + "'");
      for (int i = 0; i < years.getYear().size(); i++ ) {
        BigInteger year = years.getYear().get(i);
        statement.executeUpdate("INSERT INTO YEARS (Yearss, " + "CollaborationName) values (" +
            year +  ",'" + collaboration.getName() + "')");
      }
      
      insert = true;
    }
    catch (SQLException e) {
      insert = false;
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        this.errorMessage = closeString;
      }
    }
    return insert;
  }
  
  /**
   * Deletes an Organization form the Database. 
   * 
   * @param name The name of the collaboration to be deleted.
   * @return deleted Return true if deleted successful.
   */
  public boolean deleteCollaboration(String name) {
    Connection conn = null;
    Statement statement = null;
    boolean deleted = true;
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.executeUpdate("DELETE FROM COLLABORATIONS WHERE Name='" + name + "'");
      statement.executeUpdate("DELETE FROM COLLABORATINGORGANIZATIONS WHERE " +
          "CollaborationName='" + name + "'");
      statement.executeUpdate("DELETE FROM COLLABORATINGTYPES WHERE CollaborationName='"
          + name + "'");
      statement.executeUpdate("DELETE FROM OUTCOMETYPES WHERE CollaborationName='" 
          + name + "'");
      statement.executeUpdate("DELETE FROM YEARS WHERE CollaborationName='" + name + "'");
    }
    catch (SQLException e) {
      this.errorMessage = deleteString;
      deleted = false;
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        this.errorMessage = deleteString;
      }
    }
    return deleted;
  }
  /**
   * Creates myisern data tables in database, after deleting any pre-existing table.
   * 
   * @throws SQLException if there is a problem dropping or creating the table.
   * @return emptyTable Return true if table does not exist. 
   */
  private boolean createEmptyTable() throws SQLException {
    Connection conn = null;
    Statement statement = null;
    boolean emptyTable = true;
    
    // Now that any prior table has been deleted, create a new one.
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.execute("CREATE TABLE RESEARCHERS(Name VARCHAR(32)PRIMARY KEY, " +
          "Organization VARCHAR(32), Email VARCHAR(32), " +
          "PictureLink VARCHAR(48), BioStatement VARCHAR(256))");
    }
    catch (SQLException e) { //NOPMD
      emptyTable = false;
      this.errorMessage = tableExist;
    }
    conn = null;
    statement = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.execute("CREATE TABLE AFFILIATEDRESEARCHERS" + idKey + 
          "AffiliatedReseacher VARCHAR(32), OrganizationName VARCHAR(32) NOT NULL)");
    }
    catch (SQLException e) { //NOPMD
      emptyTable = false;
      this.errorMessage = tableExist;
    }
    conn = null;
    statement = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.execute("CREATE TABLE RESEARCHKEYWORDS" + idKey + 
          "ReseachKeyword VARCHAR(48), OrganizationName VARCHAR(32) NOT NULL)");
    }
    catch (SQLException e) { //NOPMD
      emptyTable = false;
      this.errorMessage = tableExist;
    }
    conn = null;
    statement = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.execute("CREATE TABLE ORGANIZATIONS(Name VARCHAR(32) PRIMARY KEY, " +
          "Type VARCHAR(32), Contact VARCHAR(32), Country VARCHAR(48), " +
          "HomePage VARCHAR(64), ResearchDescription VARCHAR(256) NOT NULL)");
    }
    catch (SQLException e) { //NOPMD
      emptyTable = false;
      this.errorMessage = tableExist;
    }
    conn = null;
    statement = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.execute("CREATE TABLE COLLABORATIONS(Name VARCHAR(32) PRIMARY KEY, " +
          "Description VARCHAR(256))");
    }
    catch (SQLException e) { //NOPMD
      emptyTable = false;
      this.errorMessage = tableExist;
    }
    conn = null;
    statement = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.execute("CREATE TABLE COLLABORATINGORGANIZATIONS" + idKey + 
          "CollaboratingOrganization VARCHAR(32), CollaborationName VARCHAR(32) NOT NULL)");
    }
    catch (SQLException e) { //NOPMD
      emptyTable = false;
      this.errorMessage = tableExist;
    }
    conn = null;
    statement = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.execute("CREATE TABLE COLLABORATINGTYPES" + idKey + 
          "CollaborationType VARCHAR(48), CollaborationName VARCHAR(32) NOT NULL)");
    }
    catch (SQLException e) { //NOPMD
      emptyTable = false;
      this.errorMessage = tableExist;
    }
    conn = null;
    statement = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.execute("CREATE TABLE OUTCOMETYPES" + idKey + 
          "OutcomeType VARCHAR(32), CollaborationName VARCHAR(32) NOT NULL)");
    }
    catch (SQLException e) { //NOPMD
      emptyTable = false;
      this.errorMessage = tableExist;
    }
    conn = null;
    statement = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.execute("CREATE TABLE YEARS" +
           idKey + "Yearss BIGINT, CollaborationName VARCHAR(32) NOT NULL)");
      
      emptyTable = true;
    }
    catch (SQLException e) { //NOPMD
      emptyTable = false;
      this.errorMessage = tableExist;
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        this.errorMessage = closeString;
      }
    }
    return emptyTable;
  }
  
  /**
   * Returns the Researchers.
   * 
   * @return The researchers.
   */
  public Researchers getResearchers() {
    return getDbResearchers();
  }

  /**
   * Returns the Organizations.
   * 
   * @return The organizations.
   */
  public Organizations getOrganizations() {  
    return getDbOrganizations();
  }
  
  /**
   * Returns the Collaborations.
   * 
   * @return The collaborations.
   */
  public Collaborations getCollaborations() {
    return getDbCollaborations();
  }
  
  /**
   * Set up the program. Creates the DerbyMyisernStore tables.
   * If the tables already exists, the contents of the tables are
   * restored by using the contents of the tables.
   * 
   * @return initial Return true if initial successful.
   */
  private boolean init() {
    boolean initial = true;
    try {
      if (createEmptyTable()) {
        this.researchers = new Researchers();
        this.organizations = new Organizations();
        this.collaborations = new Collaborations();
      }
      else {
        this.researchers = getDbResearchers();
        this.organizations = getDbOrganizations();
        this.collaborations = getDbCollaborations();
      }
    }
    catch (Exception e) {
      e.printStackTrace();
      this.errorMessage = "Initial faile!!!";
      initial = false;
    }
    return initial;
  }
  
  /**
   * Returns the current Collaborations instance as a String encoding of its XML representation.
   * 
   * @return xmlString Its XML String representation.
   * @throws Exception If problems occur during translation.
   */
  public String getCollaborationsXml() throws Exception {
    this.collaborationsJaxbContext = JAXBContext
        .newInstance("edu.hawaii.myisern.collaborations.jaxb");
    Marshaller marshaller = this.collaborationsJaxbContext.createMarshaller();
    DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
    dbf.setNamespaceAware(true);
    DocumentBuilder documentBuilder = dbf.newDocumentBuilder();
    Document doc = documentBuilder.newDocument();
    Collaborations collaborations = this.getDbCollaborations();
    marshaller.marshal(collaborations, doc);
    DOMSource domSource = new DOMSource(doc);
    StringWriter writer = new StringWriter();
    StreamResult result = new StreamResult(writer);
    TransformerFactory tf = TransformerFactory.newInstance();
    Transformer transformer = tf.newTransformer();
    transformer.transform(domSource, result);
    String xmlString = writer.toString();
    return xmlString;
  }
  
  /**
   * Returns the current Organizations instance as a String encoding of its XML representation.
   * 
   * @return xmlString Its XML String representation.
   * @throws Exception If problems occur during translation.
   */
  public String getOrganizationsXml() throws Exception {
    this.organizationsJaxbContext = JAXBContext
        .newInstance("edu.hawaii.myisern.organizations.jaxb");
    Marshaller marshaller = this.organizationsJaxbContext.createMarshaller();
    DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
    dbf.setNamespaceAware(true);
    DocumentBuilder documentBuilder = dbf.newDocumentBuilder();
    Document doc = documentBuilder.newDocument();
    Organizations organizations = this.getDbOrganizations();
    marshaller.marshal(organizations, doc);
    DOMSource domSource = new DOMSource(doc);
    StringWriter writer = new StringWriter();
    StreamResult result = new StreamResult(writer);
    TransformerFactory tf = TransformerFactory.newInstance();
    Transformer transformer = tf.newTransformer();
    transformer.transform(domSource, result);
    String xmlString = writer.toString();
    return xmlString;
  }

  /**
   * Returns the current Researchers instance as a String encoding of its XML representation.
   * 
   * @return xmlString Its XML String representation.
   * @throws Exception If problems occur during translation.
   */
  public String getResearchersXml() throws Exception {
    this.researchersJaxbContext = JAXBContext
        .newInstance("edu.hawaii.myisern.researchers.jaxb");
    Marshaller marshaller = this.researchersJaxbContext.createMarshaller();
    DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
    dbf.setNamespaceAware(true);
    DocumentBuilder documentBuilder = dbf.newDocumentBuilder();
    Document doc = documentBuilder.newDocument();
    Researchers researchers = this.getDbResearchers();
    marshaller.marshal(researchers, doc);
    DOMSource domSource = new DOMSource(doc);
    StringWriter writer = new StringWriter();
    StreamResult result = new StreamResult(writer);
    TransformerFactory tf = TransformerFactory.newInstance();
    Transformer transformer = tf.newTransformer();
    transformer.transform(domSource, result);
    String xmlString = writer.toString();
    return xmlString;
  }
}
